﻿CREATE PROCEDURE [dbo].[CalculeazaMaximeInSaptamani]
	@id_Medicament int,
	@numar_de_saptamani_in_statistici int
AS
	SET DATEFIRST 1;
	DECLARE @dataCurenta datetime = GetDate()
	DECLARE @partToSubtract int = 0 - @numar_de_saptamani_in_statistici
	DECLARE @dataInceput datetime = DateAdd(wk,@partToSubtract,@dataCurenta);

	WITH CTE(Id_Arhiva,RowNumber) AS 
	(
		SELECT 
			Id_Arhiva,ROW_NUMBER() OVER (PARTITION BY DATEPART(WK,DATA) ORDER BY Max(Cantitate) desc)
		FROM 
			Arhiva
		WHERE 
			Id_Medicament = @id_Medicament AND 
			DATA >= @dataInceput AND
			DATA <= @dataCurenta 
		GROUP BY Id_Arhiva,DATEPART(WK,Data)
	)
	SELECT Arhiva.*
	FROM Arhiva
	INNER JOIN CTE
	on Arhiva.Id_Arhiva = CTE.Id_Arhiva
	WHERE RowNumber = 1